import random
import time
from datetime import datetime
import pandas as pd  # 导入pandas库，用于读取Excel文件和处理数据
import requests
from bs4 import BeautifulSoup
from requests import HTTPError

from x01_stock.xx_util.MyDrissionPage import get_cookies
from x01_stock.xx_util.mysql_util import insert_db
from x01_stock.xx_util.io_util import create_directory


# 引用自D:\workspace\python_all\a01_stock\a03_FetchHeader.py
# 20240720 测试通过，可以完整的使用。
# 20240721 加入随机数去获取，cookies一次性设置三个，基本可以完成数据爬取的任务
# 20240722 DrissionPage自动获取cookies，不用手动



# 1、爬取同花顺网站当天gnzj的数据，并返回下载后的文件名列表
def fetch_gnzj():
    files_list = []
    # 使用前需手动从同花顺网站获取Cookies替换header中的内容
    # https://data.10jqka.com.cn/funds/gnzjl/#refCountId=data_55f13c2c_254
    headers1 = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
        'Host': 'data.10jqka.com.cn',
        'Referer': 'https://www.10jqka.com/',
        'Connection': 'keep-alive',
        # 'Cookie': 'historystock=300851; spversion=20130314; u_ukey=A10702B8689642C6BE607730E11E6E4A; u_uver=1.0.0; u_dpass=M6jvNigME2UCrpGldoPcPCJrM1dR6KcWBAlo2LjoAjIBHQFeKVBFZjnjmV7n9RIMHi80LrSsTFH9a%2B6rtRvqGg%3D%3D; u_did=82648751961047C788170A07F408C5CA; u_ttype=WEB; ttype=WEB; user_status=0; Hm_lvt_722143063e4892925903024537075d0d=1721390901,1721446364,1721479429; Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721390901,1721446365,1721479429; v=A-ZgJCU9vO3djWhJ9CCc6t2JN1dtxyqB_Ate5dCP0onkU4jJOFd6kcybrvSj; Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721390901,1721446365,1721477779,1721562336; Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721562336; HMACCOUNT=FFE4A3ECF588E087; Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721390904,1721446667,1721477779,1721562336; Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721562336; Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721390904,1721446667,1721477779,1721562336; Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721562336'
        'Cookie': 'v=A8LP5Xc_EAB19wxc3-OAttmFE8Mhk8ateJe60Qzb7jXgX2x99CMWvUgnCuLf;Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721623464;Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721623464;Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721623464;HMACCOUNT=7738D18D94DD39A0;Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721364736,1721364802,1721364890,1721623242;Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721364736,1721364802,1721364890,1721623242;Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721364736,1721364802,1721364890,1721623242;'
        # 'Hm_lvt_722143063e4892925903024537075d0d=1721390901; '
        #       'Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721390901; '
        #       'Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721390901; '
        #       'Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721390914; '
        #       'Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721390914; '
        #       'Hm_lpvt_722143063e4892925903024537075d0d=1721394512; '
        #       'Hm_lpvt_929f8b362150b1f77b477230541dbbc2=1721394512; '
        #       'Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721395914; '
        #       'Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721395914; '
        #       'Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721395914; '
        #       'log=; '
        #       'HMACCOUNT=FFE4A3ECF588E087; '
        #       'v=A_N1z7BSkQPhal1U-7Wh0fCegvwYKIeBQbzLHqWQT5JJpB3iLfgXOlGMWy-2'

        # 这里可以添加更多的header信息
    }

    headers2 = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
        'Host': 'data.10jqka.com.cn',
        'Referer': 'https://www.10jqka.com/',
        'Connection': 'keep-alive',
        'Cookie': 'u_ukey=A10702B8689642C6BE607730E11E6E4A; u_uver=1.0.0; u_dpass=lJjjIZdtJIfIgytCDkdZkw2AXnDqy%2FpQUjX%2BI5yveHfWfRk4sbDN%2BqorvBRBru9RHi80LrSsTFH9a%2B6rtRvqGg%3D%3D; u_did=6440EE0F97024F58975CFF610CA2726F; u_ttype=WEB; Hm_lvt_722143063e4892925903024537075d0d=1721465614,1721480639,1721534377,1721562505; Hm_lpvt_722143063e4892925903024537075d0d=1721562505; HMACCOUNT=9D5889F4AD049BDE; log=; Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721465614,1721480639,1721534377,1721562505; Hm_lpvt_929f8b362150b1f77b477230541dbbc2=1721562505; Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721447348,1721480639,1721534377,1721562505; Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721447348,1721480642,1721534436,1721562508; Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721447348,1721480642,1721534436,1721562508; Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721562512; Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721562512; Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721562512; v=A4TdeRpQvuvrRwonu48Nx7qnVQlznagHasE8S54lEM8SySo3xq14l7rRDNft'
        # 'Hm_lvt_722143063e4892925903024537075d0d=1721390901; '
        #       'Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721390901; '
        #       'Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721390901; '
        #       'Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721390914; '
        #       'Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721390914; '
        #       'Hm_lpvt_722143063e4892925903024537075d0d=1721394512; '
        #       'Hm_lpvt_929f8b362150b1f77b477230541dbbc2=1721394512; '
        #       'Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721395914; '
        #       'Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721395914; '
        #       'Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721395914; '
        #       'log=; '
        #       'HMACCOUNT=FFE4A3ECF588E087; '
        #       'v=A_N1z7BSkQPhal1U-7Wh0fCegvwYKIeBQbzLHqWQT5JJpB3iLfgXOlGMWy-2'

        # 这里可以添加更多的header信息
    }

    headers3 = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
        'Host': 'data.10jqka.com.cn',
        'Referer': 'https://www.10jqka.com/',
        'Connection': 'keep-alive',
        'Cookie': 'historystock=300851; spversion=20130314; u_ukey=A10702B8689642C6BE607730E11E6E4A; u_uver=1.0.0; u_dpass=M6jvNigME2UCrpGldoPcPCJrM1dR6KcWBAlo2LjoAjIBHQFeKVBFZjnjmV7n9RIMHi80LrSsTFH9a%2B6rtRvqGg%3D%3D; u_did=82648751961047C788170A07F408C5CA; u_ttype=WEB; ttype=WEB; user_status=0; Hm_lvt_722143063e4892925903024537075d0d=1721390901,1721446364,1721479429; Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721390901,1721446365,1721479429; Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721390901,1721446365,1721477779,1721562336; HMACCOUNT=FFE4A3ECF588E087; Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721390904,1721446667,1721477779,1721562336; Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721390904,1721446667,1721477779,1721562336; Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721562341; Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721562341; Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721562341; v=A85I_H21JKUldZCB3YL00nWBH6-VT5I0pBJGLfgXOKiMBmARYN_iWXSjlj_L'
        # 'Hm_lvt_722143063e4892925903024537075d0d=1721390901; '
        #       'Hm_lvt_929f8b362150b1f77b477230541dbbc2=1721390901; '
        #       'Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1721390901; '
        #       'Hm_lvt_60bad21af9c824a4a0530d5dbf4357ca=1721390914; '
        #       'Hm_lvt_f79b64788a4e377c608617fba4c736e2=1721390914; '
        #       'Hm_lpvt_722143063e4892925903024537075d0d=1721394512; '
        #       'Hm_lpvt_929f8b362150b1f77b477230541dbbc2=1721394512; '
        #       'Hm_lpvt_60bad21af9c824a4a0530d5dbf4357ca=1721395914; '
        #       'Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1721395914; '
        #       'Hm_lpvt_f79b64788a4e377c608617fba4c736e2=1721395914; '
        #       'log=; '
        #       'HMACCOUNT=FFE4A3ECF588E087; '
        #       'v=A_N1z7BSkQPhal1U-7Wh0fCegvwYKIeBQbzLHqWQT5JJpB3iLfgXOlGMWy-2'

        # 这里可以添加更多的header信息
    }
    cooks = get_cookies()
    headers1['Cookie'] = cooks[0]
    headers2['Cookie'] = cooks[1]
    headers3['Cookie'] = cooks[2]
    # 下载的路径
    # url = 'https://data.10jqka.com.cn/funds/gnzjl/field/tradezdf/order/desc/page/1/ajax/1/free/'
    file_path = 'D:\\workspace\\python_all\\zz_download_file\\gnzj\\'
    create_directory(file_path)

    file_name_prex = file_path + datetime.now().date().strftime('%Y-%m-%d') + '_'
    html_prex = '''
    <!DOCTYPE html>
    <html>
    <head lang="en">
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <!–[if lte IE 8]>
        <meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7" />
        <![endif]–>
        <!–[if IE 9]>
        <meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7;IE=9"/>
        <![endif]–>
        <title>概念资金流向排行-数据中心-同花顺财经</title>
    </head>
    <body>
    '''
    html_tail = '</body></html>'
    # print(file_path + file_name_prex + str(1) + '.html')
    # 使用session的方式。
    for i in range(1, 10):
        path = 'https://data.10jqka.com.cn/funds/gnzjl/field/tradezdf/order/desc/page/' + str(i) + '/ajax/1/free'
        print('准备获取第' + str(i) + '页,路径是：' + path)
        # session = requests.Session()
        header = headers1
        # 通过双重随机，防止被服务器记录规则性的动作
        sleep = i * random.randint(1, 9) * random.randint(1, 5)
        max_attempts = 5  # 最大尝试次数
        attempts = 0

        while attempts < max_attempts:
            try:
                file_full_path = file_name_prex + str(i) + '.html'
                print('file_full_path = ' + file_full_path)
                response = requests.get(path, headers=header)
                response.raise_for_status()
                # 将获取到的网页text拼接成html格式存放
                with open(file_full_path, 'w') as file:
                    file.write(html_prex + response.text + html_tail)
                    files_list.append(file_full_path)
                break  # 成功获取数据后退出循环
            except HTTPError as http_err:
                print(f"HTTP error occurred: {http_err}")
            except Exception as err:
                print(f"An error occurred: {err}")
            attempts += 1

            if id(header) == id(headers1):
                header = headers2
                print('切换到headers2')
            elif id(header) == id(headers2):
                header = headers3
                print('切换到headers3')
            else:
                header = headers1
                print('切换到headers1')

            # 可以选择在这里加入延时，避免过快重试导致的服务器压力
            if attempts < max_attempts:
                attempt_sleep = attempts * random.randint(1, 9)
                print(f"Retrying in {attempt_sleep} seconds...")
                time.sleep(attempt_sleep) # 等待时间随尝试次数增加

        if attempts == max_attempts:
            print("Failed to retrieve data after multiple attempts.")

        print('第' + str(i) + '次获取完成，准备休眠时间：' + str(sleep))
        if i < 9:
            # 等于9，证明数据已经获取完成
            time.sleep(sleep)

    return files_list


# 清洗出html文件中的数据
def parse_html(file_path, encode):
    with open(file_path, 'r', encoding='gbk') as f:
        soup = BeautifulSoup(f.read(), 'html.parser')
        table = soup.find("table")
        # print(table)
        all_tr = table.find_all('tr')
        res_tr = []
        for tr in all_tr:
            all_td = tr.find_all('td')
            res_td = []
            for td in all_td:
                res_td.append(td.get_text())
                # print(td.get_text())
            res_tr.append(res_td)
        res_tr.pop(0)
        return res_tr


# 清洗数据并导入数据库
def handle_data(data, stat_date):
    df = pd.DataFrame(data,
                      columns=['seq', 'hangye', 'hyzs', 'hyzs_zdf', 'lrzj', 'lczj', 'zj_ze', 'gsjs', 'lzg', 'lzg_zdf', 'lzg_dqj'])
    # 遍历DataFrame中的每一列
    for col in df.columns:
        # print(f"Column Name: {col}")
        # if col != 'hangye' and col != 'lzg':
        #     df[col] = df[col].str.replace("\'", "") #df[col].replace(",", "")
        if col == 'hyzs_zdf' or col == 'lzg_zdf':
            df[col] = df[col].str.replace("%", "")
            # 打印出当前列的所有数据
    #         print('处理了：' + df[col])
    # 根据清洗后的数据插入数据库
    for row in df.itertuples(index=False):
        print(row)
        sql = f"INSERT INTO stock_gnzj (stat_date,seq,hangye,hyzs,hyzs_zdf,lrzj,lczj,zj_ze,gsjs,lzg,lzg_zdf,lzg_dqj) VALUES ('{stat_date}',%s, %s, %s, %s,%s, %s, %s, %s,%s,%s, %s)"  # SQL插入语句
        insert_db(sql, row)


# A1爬取数据后生成的文件列表:调用前需要替换cookies,为了防止被网站拒绝，多搞几个cookies
files = fetch_gnzj()
# files =['D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_1.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_2.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_3.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_4.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_5.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_6.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_7.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_8.html',
#         'D:\\workspace\\python_all\\zz_download_file\\gnzj\\2024-07-25_9.html']
# print(files)
# A2根据文件列表提取出数据
for file in files:
    # list = [['101', '在线教育', '1706.03', '1.05%', '44.04', '42.40', '1.64', '125', '华扬联众', '10.01%', '8.57'],
    # ['102', '工业互联网', '1360', '1.05%', '148.84', '136.67', '12.17', '287', '动力新科', '10.00%', '4.51']]
    # 解析数据
    res_data = parse_html(file, 'utf-8')
    current_date = datetime.now().date()
    # A3 清洗数据并导入数据库
    handle_data(res_data, datetime.now().date())
